﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.Pay;
using HCQ2_Model.ShoppingCart;
using HCQ2_Model.ViewModel.VR;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    /// <summary>
    /// 购物车相关    
    /// </summary>
    public partial class T_VRShoppingCartDAL: IT_VRShoppingCartDAL
    {
        /// <summary>
        /// 获取用户购物车信息
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns></returns>
        public UserShoppingCart GetUserShoppingCart(int UserId)
        {
            //获取用户购物车信息
            string sql = string.Format("select SUM(price)totalPrice,COUNT(*)totalCommodity,(select USER_NAME from T_User where user_id={0})userName from T_VRShoppingCart where user_id={1}",UserId,UserId);
            UserShoppingCart userShoppingCart = SqlHelper.ExecuteList<UserShoppingCart>(sql)[0];

            //获取用户购物车商品信息
            sql = string.Format(@"select d.file_id id,d.file_name name,d.file_money price from T_VRShoppingCart s
            left join T_DocumentInfo d on s.file_id=d.file_id
            where s.user_id={0}", UserId);
            List<UserShoppingCartCommodity> userShoppingCartCommodityList = SqlHelper.ExecuteList<UserShoppingCartCommodity>(sql);

            userShoppingCart.commodityList = userShoppingCartCommodityList;

            return userShoppingCart;
        }

        /// <summary>
        /// 获取用户购物车商品信息
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <param name="CommodityId">选中购物车商品ID</param>
        /// <returns></returns>
        public List<T_DocumentInfo> ListUserShoppingCartCommodity(int UserId, int[] CommodityId)
        {
            string sql = string.Format(@"select * from T_DocumentInfo where file_id in(
	            select file_id from T_VRShoppingCart where user_id={0} and file_id in ({1})
            )", UserId,string.Join(",",CommodityId));

            List<T_DocumentInfo> docList = SqlHelper.ExecuteList<T_DocumentInfo>(sql);

            return docList;
        }

        /// <summary>
        /// 获取用户购物车中商品已经被购买的
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <param name="CommodityId">购物车中商品ID</param>
        /// <returns></returns>
        public List<T_VROrderDetail> ListUserPurchased(int UserId,int[] CommodityId)
        {
            string sql = string.Format(@"select * from T_VROrderDetail where order_code in (
	            select order_code from T_VROrder where user_id={0} and status=2
            ) and file_id in (select file_id from T_VRShoppingCart where user_id={0} and file_id in ({1}))", UserId,string.Join(",",CommodityId));

            List<T_VROrderDetail> detailList = SqlHelper.ExecuteList<T_VROrderDetail>(sql);

            return detailList;
        }

        /// <summary>
        /// 获取用户购物车中商品已经被删除或状态是：私有:0 /(公开)，待审核:1 /（管理员修改），3：免费共享，4：下架，6：审核未通过
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <param name="CommodityId">选中购物车商品ID</param>
        /// <returns></returns>
        public List<T_DocumentInfo> ListUserLowerShelfAndRemove(int UserId, int[] CommodityId)
        {
            string sql = string.Format(@"select * from T_DocumentInfo where file_id in(
	            select file_id from T_VRShoppingCart where user_id={0} and file_id in ({1})
            ) and (if_remove=1 or file_status<>5)", UserId,string.Join(",",CommodityId));

            List<T_DocumentInfo> docList = SqlHelper.ExecuteList<T_DocumentInfo>(sql);

            return docList;
        }

        /// <summary>
        /// 获取选中商品总价
        /// </summary>
        /// <param name="CommodityId"></param>
        /// <returns></returns>
        public Decimal GetSelectCommodityTotalMoney(int[] CommodityId)
        {
            string sql = string.Format("select SUM(file_money) from T_DocumentInfo where file_id in ({0})",string.Join(",",CommodityId));

            Object o= SqlHelper.ExecuteScalar(sql);

            return Convert.ToDecimal(o);
        }

        /// <summary>
        /// 获取选中商品中系统发布的商品总价
        /// </summary>
        /// <param name="CommodityId">选中商品ID</param>
        /// <param name="SystemUserId">系统管理员账号ID，默认：3，develop账号</param>
        /// <returns></returns>
        public Decimal GetSelectSystemCommodityTotalMoney(int[] CommodityId, string SystemUserId = "3")
        {
            string commoditys = string.Join(",", CommodityId);
            string sql = string.Format("select SUM(file_money) from T_DocumentInfo where create_id in ({0}) and file_id in ({1})",SystemUserId, commoditys);

            Object o = SqlHelper.ExecuteScalar(sql);
            if (o.GetType().Name == "DBNull") {
                return 0;
            }
            return Convert.ToDecimal(o);
        }
        /// <summary>
        ///  判断用户是否加入购物车
        /// </summary>
        /// <param name="fileId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetCartFile(int fileId, int userId)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT COUNT(0) as fileCount FROM T_VRShoppingCart WHERE user_id={0} AND file_id={1};", userId, fileId);
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }

        /// <summary>
        /// 获取选中文件进行“商品购买”的信息展示数据
        /// </summary>
        /// <param name="selectFileIdStr">选择文件的ID字符串，多个通过逗号分隔开</param>
        /// <returns></returns>
        public List<ShoppingCartListModel> GetSelectFilePayInfo(string selectFileIdStr)
        {
            //获取指定文件数据用于在“商品购买”中显示
            string sql = string.Format(@"select 
                a.file_id FileId,
                a.file_name FileName,
                case when a.pro_type=1 then 'VRPPT'
                when a.pro_type=2 then '创客'
                when a.pro_type=3 then '头记'
                when a.pro_type=4 then '校史馆' else '' end ProType,
                case when a.pro_form=1 then 'VR'
                when a.pro_form=2 then '虚拟仿真'
                when a.pro_form=3 then 'AR' else '' end ProForm,
                a.create_name CreateName,
                a.create_time CreateTime,
                b.file_url FileImgUrl,
                c.ori_price Price,
                a.supplyType SupplyType
                from T_DocumentInfo a
                left join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord
                 )t where rn=1
                )b on a.file_id=b.file_id
                left join (select * from (select ROW_NUMBER()over(partition by file_id order by op_month)rn,* from T_VRGoodDisiPrice
                )t where rn=1)c on a.file_id=c.file_id
                where a.file_id in ({0})", selectFileIdStr);

            //获取指定文件的价格期限数据
            string sql1 = string.Format(@"select 
                file_id FileId,
                op_month Month,
                ori_price Price
                from T_VRGoodDisiPrice where file_id in ({0}) order by file_id,Month", selectFileIdStr);

            List<ShoppingCartListModel> shoppingCartListModels = SqlHelper.ExecuteList<ShoppingCartListModel>(sql);

            List<ShoppingCartFileMoney> shoppingCartFileMoneys = SqlHelper.ExecuteList<ShoppingCartFileMoney>(sql1);

            foreach (ShoppingCartListModel shoppingCart in shoppingCartListModels)
            {
                shoppingCart.FileMoneys = new List<ShoppingCartFileMoney>();

                foreach (ShoppingCartFileMoney shoppingCartFileMoney in shoppingCartFileMoneys)
                {
                    if (shoppingCart.FileId == shoppingCartFileMoney.FileId) {
                        shoppingCart.FileMoneys.Add(shoppingCartFileMoney);
                    }
                }
            }

            return shoppingCartListModels;
        }

        /// <summary>
        /// 获取文件的所有期限价格信息
        /// </summary>
        /// <param name="fileId">文件ID</param>
        /// <returns></returns>
        public List<T_VRGoodDisiPrice> ListFileAllPrice(List<int> fileId)
        {
            string fileIdStr = string.Join(",",fileId);

            string sql = $"select * from T_VRGoodDisiPrice where file_id in ({fileIdStr}) order by file_id,op_month";

            var result = SqlHelper.ExecuteList<T_VRGoodDisiPrice>(sql);

            return result;
        }

        /// <summary>
        /// 获取订单下面所有商品的附件下载地址
        /// </summary>
        /// <param name="ParentOrderCode">父单号</param>
        /// <returns></returns>
        public List<PayDocFileOptionsModel> ListPayDocFileOptionsModel(string ParentOrderCode)
        {
            string webUrl = HCQ2_Common.ConfigurationHelper.AppSetting("WebUrl");
            List<PayDocFileOptionsModel> data = new List<PayDocFileOptionsModel>();

            string sql = string.Format(@"select a.file_id,a.file_name FileName,b.file_url FileImgUrl from N_OrderCommodityInfo a
            left join(select * from(select ROW_NUMBER()over(partition by file_id order by create_date desc)rn, *from T_DocBagFileRecord
             )t where rn = 1
            )b on a.file_id = b.file_id
            where OrderCode in (select OrderCode from N_Order where ParentOrderCode = '{0}')
            ", ParentOrderCode);

            DataTable fileInfo = SqlHelper.ExecuteDataTable(sql);

            sql = string.Format(@"select a.file_id,option_name OptionName,case when SUBSTRING(a.option_url,1,1)='/' then b.code_value+a.option_url else b.code_value+'/'+a.option_url end OptionUrl from T_DocFileOptions a
            left join (
            select * from T_ItemCodeMenum where item_id in (select item_id from T_ItemCode where item_code='docFileService')
            )b on a.service_id=b.code_name
            where a.file_id in (select file_id from N_OrderCommodityInfo where OrderCode in (select OrderCode from N_Order where ParentOrderCode='{0}'))
            ",ParentOrderCode);

            DataTable fileOptionInfo = SqlHelper.ExecuteDataTable(sql);

            foreach (DataRow payDocFileRow in fileInfo.Rows)
            {
                int rowFileId = Convert.ToInt32(payDocFileRow["file_id"]);
                PayDocFileOptionsModel payDocFileOptionsModel = new PayDocFileOptionsModel() {
                    FileName = Convert.ToString(payDocFileRow["FileName"]),
                    FileImgUrl = Convert.ToString(payDocFileRow["FileImgUrl"]).Replace("~", webUrl),
                    DocFileOptions = new List<PayDocFileOptionsChild>()
                };

                foreach (DataRow drow in fileOptionInfo.Rows)
                {
                    if (rowFileId == Convert.ToInt32(drow["file_id"]))
                    {
                        payDocFileOptionsModel.DocFileOptions.Add(new PayDocFileOptionsChild() { 
                            OptionName = Convert.ToString(drow["OptionName"]),
                            OptionUrl = Convert.ToString(drow["OptionUrl"])
                        });
                    }
                }

                data.Add(payDocFileOptionsModel);
            }

            return data;
        }
    }
}
